import tkinter as tk
from tkinter import ttk, messagebox, filedialog
import mysql.connector
from mysql.connector import Error
import json
from datetime import datetime
import os
import csv


class ScheduleViewer:
    def __init__(self, master):
        self.master = master
        self.master.title("值班表查询系统")
        self.master.geometry("1200x800")  # 增加高度以容纳新控件
        self.master.configure(bg="#f0f0f0")

        # MySQL连接配置
        self.db_config = {
            'host': "127.0.0.1",
            'user': "admin",
            'password': "831114399x",
            'database': "person_db"
        }

        # 初始化数据
        self.all_rooms = []
        self.selected_room = tk.StringVar()
        self.schedule_dates = []  # 存储当前房间的所有值班表日期
        self.selected_date = tk.StringVar()
        self.all_persons = []  # 存储所有人员姓名
        self.selected_person = tk.StringVar()
        self.person_count = 0  # 统计被选人员的值班次数
        self.current_schedule_data = None  # 存储当前加载的值班表数据
        self.highlighted_cells = {}  # 存储高亮单元格信息

        # 创建数据库连接
        self.conn = None
        self.connect_to_db()

        # 创建界面
        self.create_widgets()
        self.load_rooms()

        # 退出时清理资源
        self.master.protocol("WM_DELETE_WINDOW", self.on_close)

    def connect_to_db(self):
        """连接到MySQL数据库"""
        try:
            self.conn = mysql.connector.connect(**self.db_config)
            print("✅ 数据库连接成功")
        except Error as e:
            messagebox.showerror("数据库连接错误", f"连接数据库失败:\n{str(e)}")
            self.master.destroy()

    def create_widgets(self):
        """创建界面组件"""
        # 主框架
        main_frame = ttk.Frame(self.master)
        main_frame.pack(fill=tk.BOTH, expand=True, padx=20, pady=20)

        # 标题
        title_frame = ttk.Frame(main_frame)
        title_frame.pack(fill=tk.X, pady=(0, 20))
        ttk.Label(title_frame, text="值班表查询系统", font=("Helvetica", 16, "bold")).pack()

        # 控制面板
        control_frame = ttk.LabelFrame(main_frame, text="查询条件")
        control_frame.pack(fill=tk.X, pady=(0, 15))

        # 房间选择
        room_frame = ttk.Frame(control_frame)
        room_frame.pack(fill=tk.X, padx=10, pady=10)
        ttk.Label(room_frame, text="选择房间号:").pack(side=tk.LEFT)
        self.room_cb = ttk.Combobox(room_frame, textvariable=self.selected_room, width=15)
        self.room_cb.pack(side=tk.LEFT, padx=5)
        self.room_cb.bind("<<ComboboxSelected>>", self.on_room_selected)

        # 值班表选择
        schedule_frame = ttk.Frame(control_frame)
        schedule_frame.pack(fill=tk.X, padx=10, pady=(5, 10))
        ttk.Label(schedule_frame, text="选择值班表:").pack(side=tk.LEFT)
        self.schedule_cb = ttk.Combobox(schedule_frame, textvariable=self.selected_date, width=15, state='disabled')
        self.schedule_cb.pack(side=tk.LEFT, padx=5)
        self.schedule_cb.bind("<<ComboboxSelected>>", self.load_schedule)

        # 操作按钮
        btn_frame = ttk.Frame(control_frame)
        btn_frame.pack(fill=tk.X, padx=10, pady=10)
        ttk.Button(btn_frame, text="导出数据", command=self.export_data).pack(side=tk.LEFT, padx=5)
        ttk.Button(btn_frame, text="刷新列表", command=self.refresh_all).pack(side=tk.LEFT, padx=5)

        # 人员选择与统计
        person_frame = ttk.Frame(control_frame)
        person_frame.pack(fill=tk.X, padx=10, pady=10)
        ttk.Label(person_frame, text="选择人员:").pack(side=tk.LEFT)
        self.person_cb = ttk.Combobox(person_frame, textvariable=self.selected_person, width=15, state='disabled')
        self.person_cb.pack(side=tk.LEFT, padx=5)
        self.person_cb.bind("<<ComboboxSelected>>", self.on_person_selected)

        # 统计标签
        self.stat_label = ttk.Label(person_frame, text="值班次数: 0")
        self.stat_label.pack(side=tk.LEFT, padx=(20, 0))

        # 表格框架
        table_frame = ttk.LabelFrame(main_frame, text="值班表")
        table_frame.pack(fill=tk.BOTH, expand=True, pady=(0, 20))

        # 创建表格
        self.create_table(table_frame)

        # 状态栏
        self.status_bar = ttk.Label(self.master, text="就绪", relief=tk.SUNKEN, anchor=tk.W)
        self.status_bar.pack(side=tk.BOTTOM, fill=tk.X)

    def create_table(self, parent):
        """创建值班表表格"""
        # 表格
        self.tree = ttk.Treeview(parent, columns=("Date", "Weekday", "午班", "夜班1", "夜班2", "夜班3"),
                                 show="headings", height=20)
        self.tree.tag_configure('highlight', background='#FFCCCC')  # 红色高亮样式

        # 设置表头
        headers = ["日期", "星期", "午班 (12:00-18:00)", "夜班1 (18:00-22:00)", "夜班2 (22:00-02:00)",
                   "夜班3 (02:00-08:00)"]
        for i, header in enumerate(headers):
            self.tree.heading(f"#{i + 1}", text=header)
            self.tree.column(f"#{i + 1}", width=160 if i > 1 else 100)

        # 添加滚动条
        vsb = ttk.Scrollbar(parent, orient="vertical", command=self.tree.yview)
        hsb = ttk.Scrollbar(parent, orient="horizontal", command=self.tree.xview)
        self.tree.configure(yscrollcommand=vsb.set, xscrollcommand=hsb.set)

        # 布局
        self.tree.grid(row=0, column=0, sticky="nsew")
        vsb.grid(row=0, column=1, sticky="ns")
        hsb.grid(row=1, column=0, sticky="ew")

        # 配置网格权重
        parent.grid_rowconfigure(0, weight=1)
        parent.grid_columnconfigure(0, weight=1)

    def refresh_all(self):
        """刷新所有数据"""
        self.load_rooms()
        self.selected_person.set("")
        self.person_cb['state'] = 'disabled'
        self.schedule_cb['state'] = 'disabled'
        self.schedule_cb.set("")
        self.schedule_dates = []
        self.current_schedule_data = None
        self.stat_label.config(text="值班次数: 0")
        self.clear_table()
        self.status_bar.config(text="✅ 数据已刷新")

    def on_room_selected(self, event):
        """当选择房间号时"""
        room = self.selected_room.get()
        if room:
            self.load_schedule_dates(room)

    def load_rooms(self):
        """加载房间列表"""
        if not self.conn or not self.conn.is_connected():
            self.connect_to_db()
            if not self.conn:
                return

        try:
            with self.conn.cursor() as cursor:
                cursor.execute("SELECT DISTINCT room_number FROM schedules ORDER BY room_number")
                self.all_rooms = [str(r[0]) for r in cursor.fetchall()]
                self.room_cb['values'] = self.all_rooms

            if self.all_rooms:
                self.selected_room.set(self.all_rooms[0])
                self.status_bar.config(text="✅ 房间列表加载完成")
            else:
                self.status_bar.config(text="⚠️ 数据库中未找到房间数据")
        except Error as e:
            self.status_bar.config(text=f"❌ 加载房间列表错误: {str(e)}")

    def load_schedule_dates(self, room):
        """加载房间的可选值班表日期"""
        if not self.conn or not self.conn.is_connected():
            self.connect_to_db()
            if not self.conn:
                return

        try:
            with self.conn.cursor() as cursor:
                cursor.execute("""
                    SELECT DISTINCT week_start_date 
                    FROM schedules 
                    WHERE room_number = %s 
                    ORDER BY week_start_date DESC
                """, (room,))

                dates = [row[0].strftime("%Y-%m-%d") for row in cursor.fetchall()]
                self.schedule_dates = dates

                if dates:
                    self.schedule_cb['values'] = dates
                    self.schedule_cb['state'] = 'readonly'
                    self.selected_date.set(dates[0])
                    self.status_bar.config(text=f"✅ 已找到 {len(dates)} 份值班表")
                else:
                    self.schedule_cb['state'] = 'disabled'
                    self.schedule_cb.set("")
                    self.status_bar.config(text=f"❌ 房间 {room} 无值班表数据")

        except Error as e:
            self.status_bar.config(text=f"❌ 加载值班表日期错误: {str(e)}")

    def load_schedule(self, event=None):
        """加载选中的值班表"""
        date_str = self.selected_date.get()
        room = self.selected_room.get()

        if not date_str or not room:
            return

        try:
            schedule_date = datetime.strptime(date_str, "%Y-%m-%d")
        except ValueError:
            messagebox.showerror("日期格式错误", "日期格式应为YYYY-MM-DD")
            return

        if not self.conn or not self.conn.is_connected():
            self.connect_to_db()
            if not self.conn:
                return

        try:
            with self.conn.cursor() as cursor:
                query = """
                    SELECT schedule_data 
                    FROM schedules 
                    WHERE room_number = %s AND week_start_date = %s
                """
                cursor.execute(query, (room, schedule_date))
                result = cursor.fetchone()

            if not result:
                self.status_bar.config(text=f"❌ 未找到房间号 {room} 在 {date_str} 开始的值班表")
                return

            # 清空现有表格
            self.clear_table()

            # 解析JSON数据
            try:
                schedule_data = json.loads(result[0])
                self.current_schedule_data = schedule_data
            except json.JSONDecodeError as e:
                self.status_bar.config(text=f"❌ JSON解析错误: {str(e)}")
                return

            # 插入表格数据
            for day in schedule_data:
                date = day['date']
                try:
                    date_obj = datetime.strptime(date, "%Y-%m-%d")
                except ValueError:
                    continue  # 跳过无效日期

                weekday = self.get_chinese_weekday(date_obj)

                # 获取班次人员
                shifts = day['shifts']
                values = [
                    date,
                    weekday,
                    self.format_shift(shifts.get('午班', [])),
                    self.format_shift(shifts.get('夜班1', [])),
                    self.format_shift(shifts.get('夜班2', [])),
                    self.format_shift(shifts.get('夜班3', []))
                ]

                self.tree.insert("", "end", values=values)

            self.status_bar.config(text=f"✅ 已加载房间号 {room} 的值班表 ({len(schedule_data)}天)")

            # 更新人员列表
            self.load_persons_from_schedule()

        except KeyError as e:
            self.status_bar.config(text=f"❌ 数据结构错误: 缺少键 {str(e)}")
        except Error as e:
            self.status_bar.config(text=f"❌ 数据库错误: {str(e)}")

    def load_persons_from_schedule(self):
        """从当前值班表中提取所有人员姓名"""
        if not self.current_schedule_data:
            return

        person_set = set()

        for day in self.current_schedule_data:
            shifts = day.get('shifts', {})
            for shift_name, person_list in shifts.items():
                for person in person_list:
                    if isinstance(person, dict) and 'name' in person:
                        person_set.add(person['name'])

        self.all_persons = sorted(person_set)
        self.person_cb['values'] = self.all_persons
        self.person_cb['state'] = 'readonly'

        if self.all_persons:
            self.selected_person.set(self.all_persons[0])
            self.on_person_selected()
        else:
            self.person_cb.set("")

    def on_person_selected(self, event=None):
        """当选择人员时，高亮显示该人员所在的单元格"""
        selected_name = self.selected_person.get()
        if not selected_name or not self.current_schedule_data:
            return

        # 移除所有高亮
        self.clear_highlights()

        # 统计值班次数
        self.person_count = 0

        # 遍历所有行，高亮包含该人员的单元格
        for idx, item_id in enumerate(self.tree.get_children()):
            day = self.current_schedule_data[idx]
            shifts = day.get('shifts', {})

            # 检查每个班次
            for shift_idx, shift_name in enumerate(['午班', '夜班1', '夜班2', '夜班3']):
                shift_list = shifts.get(shift_name, [])
                for person in shift_list:
                    if isinstance(person, dict) and person.get('name') == selected_name:
                        self.person_count += 1

                        # 高亮该单元格
                        self.tree.set(item_id, f"#{shift_idx + 3}", self.tree.set(item_id, f"#{shift_idx + 3}"))
                        self.tree.item(item_id, tags=('highlight',))

                        # 存储高亮单元格信息
                        self.highlighted_cells[(item_id, shift_idx + 3)] = True

        # 更新统计信息
        self.stat_label.config(text=f"值班次数: {self.person_count}")

    def clear_highlights(self):
        """清除所有高亮"""
        for item_id in self.tree.get_children():
            self.tree.item(item_id, tags=())
        self.highlighted_cells = {}

    def format_shift(self, shift_list):
        """格式化班次人员信息 - 确保显示两人"""
        if not shift_list:
            return "无安排"

        names = []
        for person in shift_list:
            if isinstance(person, dict) and 'name' in person:
                names.append(person['name'])

        # 确保显示两人，如果不足两人则补充"未安排"
        if len(names) < 2:
            names.extend(["未安排"] * (2 - len(names)))

        # 只取前两人
        return ", ".join(names[:2])

    def clear_table(self):
        """清空表格"""
        for item in self.tree.get_children():
            self.tree.delete(item)
        self.clear_highlights()

    def get_chinese_weekday(self, date_obj):
        """获取中文星期几"""
        weekdays = ["星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日"]
        return weekdays[date_obj.weekday()]

    def export_data(self):
        """导出数据到CSV文件"""
        if not self.tree.get_children():
            messagebox.showwarning("无数据", "没有可导出的数据")
            return

        try:
            file_path = filedialog.asksaveasfilename(
                defaultextension=".csv",
                filetypes=[("CSV文件", "*.csv"), ("所有文件", "*.*")]
            )

            if not file_path:
                return

            with open(file_path, 'w', newline='', encoding='utf-8-sig') as f:
                writer = csv.writer(f)

                # 写表头
                headers = [self.tree.heading(col)["text"] for col in self.tree["columns"]]
                writer.writerow(headers)

                # 写数据
                for item in self.tree.get_children():
                    values = self.tree.item(item, "values")
                    writer.writerow(values)

            self.status_bar.config(text=f"✅ 数据已导出到: {os.path.basename(file_path)}")
        except Exception as e:
            messagebox.showerror("导出错误", f"导出数据失败:\n{str(e)}")

    def on_close(self):
        """关闭窗口时的清理工作"""
        if self.conn and self.conn.is_connected():
            self.conn.close()
            print("✅ 数据库连接已关闭")
        self.master.destroy()


if __name__ == "__main__":
    root = tk.Tk()
    app = ScheduleViewer(root)
    root.mainloop()
